package cn.lili.modules.member.mapper;

import cn.lili.modules.member.entity.dos.Member;
import cn.lili.modules.member.entity.vo.MemberStoreVO;
import cn.lili.modules.member.entity.vo.MemberVO;
import cn.lili.modules.member.entity.vo.RevokePartnerVo;
import cn.lili.modules.member.entity.vo.*;
import cn.lili.modules.permission.entity.dos.Role;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * 会员数据处理层
 *
 * @author Bulbasaur
 * @since 2020-02-25 14:10:16
 */
public interface MemberMapper extends BaseMapper<Member> {

	/**
	 * 获取所有的会员手机号
	 *
	 * @return 会员手机号
	 */
	@Select("select m.mobile from li_member m")
	List<String> getAllMemberMobile();

	@Select("select a.*,b.end_time MemberEndTime,m.level gradeName,w.member_wallet from li_member a"
			+ " left join li_grade_level b on a.id = b.member_id left join li_membership_card m on m.id = b.grade_id "
			+ " left join li_member_wallet w on w.member_id = a.id ${ew.customSqlSegment}")
	IPage<MemberVO> pageByMemberVO(IPage<MemberVO> page, @Param(Constants.WRAPPER) Wrapper<Member> queryWrapper);

	@Select("SELECT a.*,e1.extension,b.end_time MemberEndTime,m.LEVEL gradeName,w1.member_wallet rechargeMoney ,w2.member_wallet promoteMoney,w3.member_wallet saleMoney ,s.store_name storeName"
			+ " FROM li_member a LEFT JOIN li_grade_level b ON a.id = b.member_id"
			+ " LEFT JOIN li_membership_card m ON m.id = b.grade_id"
			+ " LEFT JOIN li_member_wallet w1 ON w1.member_id = a.id and w1.owner = 'RECHARGE'"
			+ " LEFT JOIN li_member_wallet w2 ON w2.member_id = a.id and w2.owner = 'PROMOTE'"
			+ " LEFT JOIN li_member_wallet w3 ON w3.member_id = a.id and w3.owner = 'SALE'"
			+ " LEFT JOIN li_extension e1 ON e1.member_id = a.id AND e1.delete_flag = 0 "
			+ " left join li_store s on s.member_id = a.id ${ew.customSqlSegment}")
	IPage<MemberVO> pageByBMemberVO(IPage<MemberVO> page, @Param(Constants.WRAPPER) Wrapper<Member> queryWrapper);

	@Select("SELECT DISTINCT a.*,e1.extension,b.end_time MemberEndTime,m.LEVEL gradeName,w1.member_wallet rechargeMoney ,w2.member_wallet promoteMoney"
			+ " FROM li_member a LEFT JOIN li_grade_level b ON a.id = b.member_id and b.delete_flag = '0' "
			+ " LEFT JOIN li_membership_card m ON m.id = b.grade_id"
			+ " LEFT JOIN li_member_wallet w1 ON w1.member_id = a.id and w1.owner = 'RECHARGE'"
			+ " LEFT JOIN li_member_wallet w2 ON w2.member_id = a.id and w2.owner = 'PROMOTE'"
			+ " LEFT JOIN li_extension e1 ON e1.member_id = a.id "
			+ " LEFT JOIN li_partner p ON p.member_id = a.id and p.delete_flag = '0' AND p.partner_state = '0' ${ew.customSqlSegment}")
	IPage<MemberVO> pageByCMemberVO(IPage<MemberVO> page, @Param(Constants.WRAPPER) Wrapper<Member> queryWrapper);

	@Select("SELECT DISTINCT a.*,e1.extension,b.end_time MemberEndTime,m.LEVEL gradeName,w1.member_wallet rechargeMoney ,w2.member_wallet promoteMoney"
			+ " FROM li_member a LEFT JOIN li_grade_level b ON a.id = b.member_id and b.delete_flag = '0' "
			+ " LEFT JOIN li_membership_card m ON m.id = b.grade_id"
			+ " LEFT JOIN li_member_wallet w1 ON w1.member_id = a.id and w1.owner = 'RECHARGE'"
			+ " LEFT JOIN li_member_wallet w2 ON w2.member_id = a.id and w2.owner = 'PROMOTE'"
			+ " LEFT JOIN li_extension e1 ON e1.member_id = a.id "
			+ " LEFT JOIN li_partner p ON p.member_id = a.id and p.delete_flag = '0' AND p.partner_state = '0' ${ew.customSqlSegment}")
	List<MemberVO> listByMember(@Param(Constants.WRAPPER) Wrapper<Member> queryWrapper);

	@Select("SELECT e1.extension, p.recommend_phone AS recommendPhone,m2.nick_name AS recommendName,"
			+ " ls.id as store_id,ls.store_name AS storeName,ls.store_address_path storeAddressPath, m1.real_name AS nameExte, m1.mobile AS mobileExte,"
			+ " m.id,m.mobile,m.nick_name,m.username,m.promotion_code,m.real_name,m.link_mobile,m.location, "
			+ " p.id AS partnerId, p.partner_type, p.partner_state, p.audit_state, "
			+ " p.region AS partnerRegion, p.region_id AS partnerRegionId, p.begin_time, p.end_time, "
			+ " m.disable_time AS partner_state_time, lro.name AS role_name"
			+ " FROM li_partner p LEFT JOIN li_member m ON p.member_id = m.id LEFT JOIN li_member m2 ON p.recommend_phone = m2.mobile "
			+ " LEFT JOIN li_extension e1 ON e1.member_id = m.id AND e1.delete_flag = 0 LEFT JOIN li_member m1 ON e1.extension = m1.promotion_code  "
			+ " LEFT JOIN li_role lro on lro.id = p.role_id "
			+ " LEFT JOIN li_store ls ON ls.member_id = m.id AND ls.delete_flag = '0' ${ew.customSqlSegment}")
	IPage<MemberVO> updatePartner(IPage<MemberVO> page, @Param(Constants.WRAPPER) Wrapper<Member> queryWrapper);

	@Select("SELECT RoundDown (sum( rechargeMoney), 2 ) AS rechargeMoney,"
			+ " RoundDown (sum( promoteMoney), 2 ) AS promoteMoney,"
			+ " RoundDown (sum( promoteFwMoney), 2 ) AS promoteFwMoney,"
			+ " RoundDown (sum( promoteHyMoney), 2 ) AS promoteHyMoney,"
			+ " RoundDown (sum(saleMoney), 2 ) AS saleMoney, RoundDown (sum( allMoney), 2 ) AS allMoney,"
			+ " RoundDown (sum( allRechargeMoney), 2 ) AS allRechargeMoney,"
			+ " RoundDown (sum( allYiBeiMoney), 2 ) AS allYiBeiMoney from ( SELECT"
			+ " lmw.rechargeMoney AS rechargeMoney, lmw.promoteMoney AS promoteMoney,"
			+ " lmw.promoteFwMoney AS promoteFwMoney, lmw.promoteHyMoney AS promoteHyMoney,"
			+ " lmw.saleMoney AS saleMoney,"
			+ " lmw.rechargeMoney + lmw.promoteMoney + lmw.promoteFwMoney + lmw.promoteHyMoney + lmw.saleMoney AS allMoney,"
			+ " lr.recharge_money AS allRechargeMoney, lr.yi_bei AS allYiBeiMoney "
			+ " FROM li_partner p LEFT JOIN li_member m ON p.member_id = m.id LEFT JOIN li_member m2 ON p.recommend_phone = m2.mobile "
			+ " LEFT JOIN li_extension e ON e.extension = m.promotion_code and e.delete_flag = 0 "
			+ " LEFT JOIN li_extension e1 ON e1.member_id = m.id AND e1.delete_flag = 0 LEFT JOIN li_member m1 ON e1.extension = m1.promotion_code  "
			+ " LEFT JOIN li_grade_level lgl ON lgl.member_id = m.id and lgl.delete_flag = 0 "
			+ " LEFT JOIN li_membership_card lmc ON lmc.id = lgl.grade_id LEFT JOIN ( SELECT "
			+ " member_id, SUM( IF( lmw.OWNER = 'RECHARGE', lmw.member_wallet, 0 )) AS rechargeMoney, "
			+ " SUM( IF( lmw.OWNER = 'PROMOTE', lmw.member_wallet, 0 )) AS promoteMoney, "
			+ " SUM( IF( lmw.OWNER = 'PROMOTE_FW', lmw.member_wallet, 0 )) AS promoteFwMoney, "
			+ " SUM( IF( lmw.OWNER = 'PROMOTE_HY', lmw.member_wallet, 0 )) AS promoteHyMoney, "
			+ " SUM( IF( lmw.OWNER = 'SALE', lmw.member_wallet, 0 )) AS saleMoney FROM "
			+ " li_member_wallet lmw GROUP BY lmw.member_id ) lmw ON lmw.member_id = m.id"
			+ " LEFT JOIN li_role lro on lro.id = p.role_id "
			+ " LEFT JOIN li_store ls ON ls.member_id = m.id AND ls.delete_flag = '0 ' LEFT JOIN ("
			+ " SELECT sum(recharge_money) recharge_money, member_id, sum(price) price, sum(yi_bei) yi_bei FROM li_recharge WHERE pay_status = 'PAID' "
			+ " GROUP BY member_id ) lr ON lr.member_id = m.id ${ew.customSqlSegment} )aaa")
	MemberVO countPartner(@Param(Constants.WRAPPER) Wrapper<Member> queryWrapper);

	@Select("SELECT lmc.name AS gradeName, m1.real_name AS nameExte, m1.mobile AS mobileExte, "
			+ " lmc1.NAME AS gradeNameExte, r1.name as partnerExte, m.*, p.id AS partnerId, "
			+ " p.partner_type, lro.role_name as partnerName, p.partner_state, p.audit_state, "
			+ " p.region AS partnerRegion, p.region_id AS partnerRegionId, p.begin_time, p.end_time, "
			+ " p.application_time, p.audit_time, p.audit_remark, r.recharge_money as duanKouFeiXJ,"
			+ " r.price as duanKouFeiHB FROM li_partner p LEFT JOIN li_member m ON p.member_id = m.id "
			+ " LEFT JOIN li_extension e1 ON e1.member_id = m.id LEFT JOIN li_member m1 ON e1.extension = m1.promotion_code "
			+ " LEFT JOIN li_partner p1 on p1.member_id = m1.id LEFT JOIN li_role r1 on r1.id = p1.role_id"
			+ " LEFT JOIN li_role lro ON lro.id = p.role_id LEFT JOIN li_grade_level lgl ON lgl.member_id = m.id "
			+ " LEFT JOIN li_membership_card lmc ON lmc.id = lgl.grade_id "
			+ " LEFT JOIN li_grade_level lgl1 ON lgl1.member_id = m1.id "
			+ " LEFT JOIN li_membership_card lmc1 ON lmc1.id = lgl1.grade_id LEFT JOIN ("
			+ " SELECT recharge_money, member_id,price,yi_bei FROM li_recharge "
			+ " WHERE recharge_type = '5' AND pay_status = 'PAID' ORDER BY create_time DESC limit 1"
			+ " ) r ON r.member_id = m.id ${ew.customSqlSegment}")
	IPage<MemberVO> getPartnerList(IPage<MemberVO> page, @Param(Constants.WRAPPER) Wrapper<Member> queryWrapper);

	@Select("SELECT RoundDown ( sum( rechargeMoney ), 2 ) AS rechargeMoney,"
			+ "RoundDown ( sum( promoteMoney ), 2 ) AS promoteMoney,"
			+ "RoundDown ( sum( promoteFwMoney ), 2 ) AS promoteFwMoney,"
			+ "RoundDown ( sum( promoteHyMoney ), 2 ) AS promoteHyMoney,"
			+ "RoundDown ( sum( saleMoney ), 2 ) AS saleMoney," + "RoundDown ( sum( allMoney ), 2 ) AS allMoney,"
			+ "RoundDown ( SUM( allRechargeMoney ), 2 ) AS allRechargeMoney,"
			+ "RoundDown ( SUM( allYiBeiMoney ), 2 ) AS allYiBeiMoney FROM (" + "SELECT " + " lmw.rechargeMoney, "
			+ " lmw.promoteMoney, " + " lmw.promoteFwMoney, " + " lmw.promoteHyMoney, " + " lmw.saleMoney ,"
			+ " lmw.rechargeMoney +lmw.promoteMoney +lmw.promoteFwMoney +lmw.promoteHyMoney+lmw.saleMoney AS allMoney ,"
			+ " lr.recharge_money AS allRechargeMoney , lr.yi_bei AS allYiBeiMoney"
			+ " FROM li_partner p LEFT JOIN li_member m ON p.member_id = m.id "
			+ " LEFT JOIN li_extension e ON e.extension = m.promotion_code and e.delete_flag = 0 "
			+ " LEFT JOIN li_extension e1 ON e1.member_id = m.id LEFT JOIN li_member m1 ON e1.extension = m1.promotion_code"
			+ " LEFT JOIN li_grade_level lgl ON lgl.member_id = m.id and lgl.delete_flag = 0 "
			+ " LEFT JOIN li_membership_card lmc ON lmc.id = lgl.grade_id LEFT JOIN ( SELECT "
			+ " member_id, SUM(IF( lmw.OWNER = 'RECHARGE', lmw.member_wallet, 0 )) AS rechargeMoney, "
			+ " SUM(IF( lmw.OWNER = 'PROMOTE', lmw.member_wallet, 0 )) AS promoteMoney, "
			+ " SUM(IF( lmw.OWNER = 'PROMOTE_FW', lmw.member_wallet, 0 )) AS promoteFwMoney, "
			+ " SUM(IF( lmw.OWNER = 'PROMOTE_HY', lmw.member_wallet, 0 )) AS promoteHyMoney, "
			+ " SUM(IF( lmw.OWNER = 'SALE', lmw.member_wallet, 0 )) AS saleMoney FROM "
			+ " li_member_wallet lmw GROUP BY lmw.member_id ) lmw ON lmw.member_id = m.id"
			+ " LEFT JOIN li_role lro on lro.id = p.role_id "
			+ " LEFT JOIN li_store ls ON ls.id = m.store_id AND ls.delete_flag = '0 ' LEFT JOIN ("
			+ " SELECT sum(recharge_money) recharge_money, member_id, sum(price) price, sum(yi_bei) yi_bei FROM li_recharge WHERE pay_status = 'PAID' "
			+ " GROUP BY member_id ) lr ON lr.member_id = m.id ${ew.customSqlSegment}) aa")
	MemberVO updatePartnerAll(@Param(Constants.WRAPPER) QueryWrapper<Member> queryWrapper);

	// 获取用户信息
	@Select("select s.is_have_contract,p.end_time as partnerEndTime,g.grade_id,g.owner,c.level as gradeName,DATE_FORMAT(g.end_time,'%Y-%m-%d') as gradeEndTime,"
			+ " m.*,s.id as storeId,s.store_name,s.store_logo,s.store_address_path,s.store_address_detail,p.partner_type,p.partner_state as partnerState from li_member m "
			+ "left join li_partner p on p.member_id = m.id and p.partner_state = 0 and p.delete_flag = 0 and NOW() < DATE_FORMAT(p.end_time,'%Y-%m-%d') and (p.audit_state = 'PASS' or p.audit_state is null) "
			+ "left join li_grade_level g on g.member_id = m.id and g.grade_state = 0 and g.delete_flag = 0 and NOW() < DATE_FORMAT(g.end_time,'%Y-%m-%d') "
			+ "left join li_membership_card c on c.id = g.grade_id LEFT JOIN li_store s on s.member_id = m.id and s.delete_flag = '0' ${ew.customSqlSegment}")
	MemberVO findByUsername(@Param(Constants.WRAPPER) QueryWrapper<Member> queryWrapper);

	// 获取用户信息
	@Select("select g.grade_id,g.owner,c.level as gradeName,c.name AS gradeText,DATE_FORMAT(g.end_time,'%Y-%m-%d') as gradeEndTime,m.*,p.partner_type from li_member m "
			+ "left join li_partner p on p.member_id = m.id and p.partner_state = 0 and p.delete_flag = 0 and NOW() < DATE_FORMAT(p.end_time,'%Y-%m-%d') "
			+ "left join li_grade_level g on g.member_id = m.id and g.grade_state = 0 and g.delete_flag = 0 and NOW() < DATE_FORMAT(g.end_time,'%Y-%m-%d') "
			+ "left join li_membership_card c on c.id = g.grade_id ${ew.customSqlSegment}")
	MemberVO findByUsernameAndRole(@Param(Constants.WRAPPER) QueryWrapper<Member> queryWrapper);

	/**
	 * 清空回收站
	 */
	@Update("UPDATE li_member set delete_flag = 1 where disabled = 0")
	void cleanMember();

	@Select("select a.id,a.mobile,a.store_id,b.store_name,b.store_address_detail,b.member_id from li_member a left join li_store b on a.id = b.member_id ${ew.customSqlSegment}")
	IPage<MemberStoreVO> getmemSto(IPage<MemberStoreVO> page,
			@Param(Constants.WRAPPER) Wrapper<MemberStoreVO> queryWrapper);

	/**
	 * 更新会员的身份证号和姓名
	 */
	@Update("UPDATE li_member set real_name = #{real_name},id_card_number =#{id_card_number} where id = #{id}")
	void authentication(@Param("real_name") String real_name, @Param("id_card_number") String id_card_number,
			@Param("id") String id);

	/**
	 * 更新会员的身份证号和姓名
	 */
	@Update("UPDATE li_member set real_name = #{real_name},id_card_number =#{id_card_number} where id = #{id}")
	void authVerify(@Param("real_name") String real_name, @Param("id_card_number") String id_card_number,
			@Param("id") String id);

	@Select("SELECT r.id,r.name from li_role r left join li_user_role ur on ur.role_id = r.id"
			+ " left join li_admin_user u on u.id = ur.user_id left join li_member m on m.mobile = u.mobile"
			+ " where m.id = #{id}")
	List<Role> findRoleById(String id);

	@Select("SELECT DATE_FORMAT(b.end_time,'%Y-%m-%d %H:%i:%S') MemberEndTime FROM li_member a"
			+ " LEFT JOIN li_grade_level b ON a.id = b.member_id where b.delete_flag = 0 and a.id=#{id} limit 1")
	MemberDetailsVo getMemberDetailsVo(String id);

	@Select("SELECT e.*,(case e.incomeType WHEN 4 THEN '充值 ' WHEN 43 THEN '充值银卡 ' WHEN 44 THEN '充值金卡 ' WHEN 99 THEN '转入 'END) AS incomeTypeName FROM( SELECT b.location as location,b.nick_name AS nickName,b.mobile AS mobile,c.NAME AS gradeIdName,a.sn AS receivableNo, DATE_FORMAT(a.create_time,'%Y-%m-%d %H:%i:%S') AS incomeTime,'99' AS incomeType,"
			+ "a.transfer_money AS yiBei,0 AS cash,a.payee AS memberId FROM li_transfer a "
			+ "LEFT JOIN li_member b ON a.payer = b.id LEFT JOIN li_membership_card c ON b.grade_id = c.id UNION ALL "
			+ "SELECT b.location as location,b.nick_name,b.mobile,c.NAME,a.recharge_sn,a.create_time,recharge_type,a.yi_bei,a.recharge_money,a.member_id "
			+ "FROM li_recharge a LEFT JOIN li_member b ON a.member_id = b.id "
			+ "LEFT JOIN li_membership_card c ON b.grade_id = c.id WHERE a.pay_status = 'PAID') e ${ew.customSqlSegment}")
	IPage<MemberIncomeVo> getIncomeByPage(IPage<MemberIncomeVo> page,
			@Param(Constants.WRAPPER) Wrapper<MemberIncomeVo> queryWrapper);

	@Select("SELECT RoundDown(sum(e.yiBei),2) AS sumyiBei ,RoundDown(sum(e.cash),2) sumCash FROM( SELECT b.nick_name AS nickName,b.mobile AS mobile,c.NAME AS gradeIdName,a.sn AS receivableNo,a.create_time AS incomeTime,'99' AS incomeType,"
			+ "a.transfer_money AS yiBei,0 AS cash,a.payee AS memberId FROM li_transfer a "
			+ "LEFT JOIN li_member b ON a.payee = b.id LEFT JOIN li_membership_card c ON b.grade_id = c.id UNION ALL "
			+ "SELECT b.nick_name,b.mobile,c.NAME,a.recharge_sn,a.create_time,recharge_type,a.yi_bei,a.recharge_money,a.member_id "
			+ "FROM li_recharge a LEFT JOIN li_member b ON a.member_id = b.id "
			+ "LEFT JOIN li_membership_card c ON b.grade_id = c.id ) e ${ew.customSqlSegment}")
	Map<String, Object> getIncomeByPageCount(@Param(Constants.WRAPPER) Wrapper<MemberIncomeVo> queryWrapper);

	@Select("SELECT e.*, (case e.expenditureType WHEN '3 ' THEN '支出会员费 ' WHEN 'OFFLINE ' THEN '线下订单 ' WHEN 'NORMAL ' THEN '线上订单 ' WHEN 'VIRTUAL ' THEN '虚拟订单 ' END) AS expenditureTypeName FROM ( SELECT b.location as location,b.nick_name as nickName,b.mobile as mobile,c.NAME as gradeIdName,a.sn as receivableNo,DATE_FORMAT(a.payment_time,'%Y-%m-%d %H:%i:%S') as expenditureTime,a.order_type as expenditureType, "
			+ "a.order_status as orderStatus,a.flow_price as yiBei,a.member_id as memberId FROM li_order a "
			+ "LEFT JOIN li_member b ON a.member_id = b.id "
			+ "LEFT JOIN li_membership_card c ON b.grade_id = c.id UNION ALL "
			+ "SELECT b.location as location,b.nick_name,b.mobile,c.NAME,a.recharge_sn,a.create_time,recharge_type,'PUNCHING',a.recharge_money,a.member_id "
			+ "FROM li_recharge a LEFT JOIN li_member b ON a.member_id = b.id "
			+ "LEFT JOIN li_membership_card c ON b.grade_id = c.id ) e ${ew.customSqlSegment}")
	IPage<MemberExpenditureVo> getBranchByPage(IPage<MemberExpenditureVo> page,
			@Param(Constants.WRAPPER) Wrapper<MemberExpenditureVo> queryWrapper);

	@Select("SELECT RoundDown( sum(e.yiBei),2) AS sumyiBei FROM ( SELECT b.nick_name as nickName,b.mobile as mobile,c.NAME as gradeIdName,a.sn as receivableNo,a.payment_time as expenditureTime,a.order_type as expenditureType, "
			+ "a.order_status as orderStatus,a.flow_price as yiBei,a.member_id as memberId FROM li_order a "
			+ "LEFT JOIN li_member b ON a.member_id = b.id "
			+ "LEFT JOIN li_membership_card c ON b.grade_id = c.id UNION ALL "
			+ "SELECT b.nick_name,b.mobile,c.NAME,a.recharge_sn,a.create_time,recharge_type,'PUNCHING',a.recharge_money,a.member_id "
			+ "FROM li_recharge a LEFT JOIN li_member b ON a.member_id = b.id "
			+ "LEFT JOIN li_membership_card c ON b.grade_id = c.id ) e ${ew.customSqlSegment}")
	Map<String, Object> getBranchByPageCount(@Param(Constants.WRAPPER) Wrapper<MemberExpenditureVo> queryWrapper);

	@Select("SELECT a.* FROM(SELECT b.id, b.id as memberId,b.username as username,b.nick_name as nickName,b.mobile as mobile,c.name as gradeIdName,b.location as location, DATE_FORMAT(b.create_time,'%Y-%m-%d %H:%i:%S') as createTime, ifNull( DATE_FORMAT(b.disable_time,'%Y-%m-%d %H:%i:%S') ,'') as disableTime, DATE_FORMAT(d.end_time,'%Y-%m-%d %H:%i:%S') as memberEndTime,b.type as type ,c.level as level,b.delete_flag,b.disabled"
			+ " FROM li_member b "
			+ " LEFT JOIN li_grade_level d ON b.id = d.member_id and d.delete_flag = 0 and d.grade_state = 0 and b.delete_flag = 0 and b.disabled = 0 "
			+ " LEFT JOIN li_membership_card c ON d.grade_id = c.id"
			+ " LEFT JOIN li_partner e ON e.member_id = b.id and e.delete_flag = '0 ' AND e.pay_state = 'PASS ' "
			+ " WHERE ISNULL(e.id))a ${ew.customSqlSegment}")
	IPage<MemberAbnormalVo> getDisableMemberPage(IPage<MemberAbnormalVo> page,
			@Param(Constants.WRAPPER) Wrapper<MemberAbnormalVo> queryWrapper);

	@Select("SELECT a.begin_time as registerTime,a.end_time as expireTime,b.recharge_money as hasPayPortFee FROM li_partner a"
			+ " LEFT JOIN ( select * from ( SELECT distinct(create_time) create_time, "
			+ " recharge_money,member_id FROM li_recharge"
			+ " WHERE recharge_type = '5' AND pay_status = 'PAID' order by create_time desc"
			+ " ) r group by r.member_id) b on b.member_id = a.member_id"
			+ " WHERE a.member_id = #{memberId} and a.delete_flag = 0 and partner_state= 0 ")
	RevokePartnerVo getDetail(String memberId);

	@Select("SELECT COUNT( b.id ), IFNULL(c.`name`, '普通会员 ') AS gradeName, a.* FROM li_member a LEFT JOIN li_extension b ON a.id = b.member_id AND b.delete_flag = 0 LEFT JOIN li_membership_card c ON c.id = a.grade_id LEFT JOIN li_partner d on d.member_id = a.id AND d.delete_flag = 0 AND partner_type <>1 ${ew.customSqlSegment}")
	IPage<MemberVO> noPartnerMemberList(Page<MemberVO> page,
			@Param(Constants.WRAPPER) QueryWrapper<Member> queryWrapper);

	@Select(" SELECT b.id as id, b.username as username, b.nick_name as nickName,s.store_address_detail,s.store_address_path, "
			+ " b.mobile as mobile, e.name as gradeName, b.location as location, a.extension, "
			+ " DATE_FORMAT(a.create_time,'%Y-%m-%d %H:%i:%S') as promoteDate , "
			+ " RoundDown(c.member_wallet,2) AS rechargeMoney, "
			+ " RoundDown(SUM(cz.czhyfy),2) AS rechargeMemberCommission, "
			+ " RoundDown(SUM(cz.czhy),2) AS rechargeMember, RoundDown(SUM(xf.xf),2) AS consumption, "
			+ " RoundDown(SUM(xf.xffy),2) AS consumptionCommission,s.store_name FROM li_extension a "
			+ " LEFT JOIN li_member b ON a.member_id = b.id "
			+ " LEFT JOIN li_partner p ON p.member_id = b.id AND p.delete_flag = '0' AND p.partner_state = '0' "
			+ " LEFT JOIN li_member_wallet c ON c.member_id = b.id and c.owner = 'RECHARGE ' "
			+ " left join li_grade_level d on b.id = d.member_id and d.delete_flag = 1 "
			+ " left join li_membership_card e on e.id = d.grade_id"
			+ " LEFT JOIN li_store s ON s.member_id = b.id  LEFT JOIN ("
			+ " SELECT r.member_id,sum(r.recharge_money) czhy,sum(wd.money ) czhyfy from li_recharge r "
			+ " left join li_wallet_detail wd on wd.sn = r.recharge_sn AND wd.payee = '${memberId}' and wd.transaction_type like 'WALLET_COMMISSION%'"
			+ " where r.pay_status = 'PAID' AND r.recharge_type = '3' "
			+ " GROUP BY r.member_id ) cz on cz.member_id = b.id LEFT JOIN ("
			+ " SELECT o.member_id,sum(o.flow_price) xf,sum(wd.money ) xffy from li_order o "
			+ " left join li_wallet_detail wd on wd.sn = o.sn AND wd.payee = '${memberId}' and wd.transaction_type like 'WALLET_COMMISSION%'"
			+ " where o.pay_status = 'PAID' GROUP BY o.member_id"
			+ " ) xf on xf.member_id = b.id ${ew.customSqlSegment}")
	IPage<MemberVO> getDisableMemberPageAll(Page<MerchantsMemberVo> page,
			@Param(Constants.WRAPPER) QueryWrapper<MerchantsMemberVo> queryWrapper, @Param("memberId") String memberId);

	@Select(" SELECT b.id as id, b.username as username, b.nick_name as nickName, "
			+ " b.mobile as mobile, e.name as gradeName, b.location as location, "
			+ " DATE_FORMAT(b.create_time,'%Y-%m-%d %H:%i:%S') as promoteDate , "
			+ " RoundDown(c.member_wallet,2) AS rechargeMoney, "
			+ " RoundDown(SUM(cz.czhyfy),2) AS rechargeMemberCommission, "
			+ " RoundDown(SUM(cz.czhy),2) AS rechargeMember, RoundDown(SUM(xf.xf),2) AS consumption, "
			+ " RoundDown(SUM(xf.xffy),2) AS consumptionCommission FROM li_member b "
			+ " LEFT JOIN li_partner p ON p.member_id = b.id AND p.delete_flag = '0' AND p.partner_state = '0' "
			+ " LEFT JOIN li_member_wallet c ON c.member_id = b.id and c.owner = 'RECHARGE ' "
			+ " left join li_grade_level d on b.id = d.member_id and d.delete_flag = 0 "
			+ " left join li_membership_card e on e.id = d.grade_id LEFT JOIN (SELECT lwd.payer, "
			+ " SUM(lwd.money) AS czhyfy , lr.price AS czhy FROM li_wallet_detail lwd "
			+ " LEFT JOIN li_recharge lr ON lr.recharge_sn = lwd.sn WHERE lr.id IS NOT NULL "
			+ " AND lr.pay_status = 'PAID ' AND lr.recharge_type = '3 ' and lwd.payee = '${memberId} ' GROUP BY "
			+ " lwd.payer) cz on cz.payer = b.id LEFT JOIN (SELECT lwd.payer, SUM(lwd.money) AS xffy, "
			+ " lo.goods_price AS xf FROM li_wallet_detail lwd LEFT JOIN li_order lo ON lo.sn = lwd.sn "
			+ "WHERE lo.id IS NOT NULL AND lo.pay_status = 'PAID ' and lwd.payee = '${memberId} ' GROUP BY "
			+ " lwd.payer) xf on xf.payer = b.id ${ew.customSqlSegment}")
	IPage<MemberVO> getDisableMemberPageAll2(Page<MerchantsMemberVo> page,
			@Param(Constants.WRAPPER) QueryWrapper<MerchantsMemberVo> queryWrapper, @Param("memberId") String memberId);

	@Select(" SELECT lm.id AS memberId, lm.nick_name AS nickName, lm.mobile AS mobile, "
			+ " IFNULL(lmc.name,'普通会员') AS gradeIdName, lm.location AS location, a.payee, a.receivableNo,"
			+ " DATE_FORMAT(a.incomeTime,'%Y-%m-%d %H:%i:%S') AS incomeTime, a.OWNER,"
			+ " RoundDown ( sum( a.money ), 2 ) AS money, RoundDown ( a.flowPrice, 2 ) AS flowPrice , a.incomeTypeName "
			+ " FROM (SELECT lo.member_id AS memberId, lwd.payee AS payee, "
			+ " lwd.sn AS receivableNo, lwd.create_time AS incomeTime, lwd.transaction_type AS incomeType, "
			+ " lwd.payee_owner AS OWNER, lwd.money, lo.goods_price AS flowPrice ,"
			+ " '订单' AS incomeTypeName FROM li_wallet_detail lwd "
			+ " LEFT JOIN li_order lo ON lo.sn = lwd.sn WHERE lwd.payee_owner LIKE 'PROMOTE%' "
			+ " AND lo.id IS NOT NULL UNION ALL SELECT lt.payer AS memberId, lwd.payee AS payee, "
			+ " lwd.sn AS receivableNo, lwd.create_time AS incomeTime, lwd.transaction_type AS incomeType, "
			+ " lwd.payee_owner AS OWNER, lwd.money, lt.transfer_money AS flowPrice,"
			+ " '转账' AS incomeTypeName FROM li_wallet_detail lwd "
			+ " LEFT JOIN li_transfer lt ON lt.sn = lwd.sn WHERE lwd.payee_owner LIKE 'PROMOTE%' "
			+ " AND lt.id IS NOT NULL UNION ALL SELECT lmwa.member_id AS memberId, lwd.payee AS payee, "
			+ " lwd.sn AS receivableNo, lwd.create_time AS incomeTime, lwd.transaction_type AS incomeType, "
			+ " lwd.payee_owner AS OWNER, lwd.money AS money, lmwa.apply_price AS flowPrice,"
			+ " '提现' AS incomeTypeName FROM li_wallet_detail lwd "
			+ " LEFT JOIN li_member_withdraw_apply lmwa ON lmwa.sn = lwd.sn WHERE "
			+ " lwd.payee_owner LIKE 'PROMOTE%' AND lmwa.id IS NOT NULL UNION ALL SELECT "
			+ " lr.member_id AS memberId, lwd.payee AS payee, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS incomeTime, lwd.transaction_type AS incomeType, lwd.payee_owner AS OWNER, "
			+ " lwd.money AS money, lr.recharge_money AS flowPrice, '会员费' AS incomeTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_recharge lr ON lr.recharge_sn = lwd.sn WHERE "
			+ " lwd.payee_owner LIKE 'PROMOTE%' AND lr.id IS NOT NULL ) AS a "
			+ " LEFT JOIN li_member lm ON a.memberId = lm.id "
			+ " LEFT JOIN li_membership_card lmc ON lmc.id = lm.grade_id ${ew.customSqlSegment}")
	IPage<MemberIncomeVo> getIncomePromoteByPage(Page<Object> initPage,
			@Param(Constants.WRAPPER) QueryWrapper<MemberIncomeVo> queryWrapper);

	@Select("SELECT RoundDown(sum(b.money),2) AS sumMoney, RoundDown(sum(b.flowPrice) ,2) AS sumFlowPrice "
			+ " FROM (SELECT RoundDown ( sum( a.money ), 2 ) AS money,a.flowPrice FROM (SELECT lo.member_id AS memberId, lwd.payee AS payee, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS incomeTime, lwd.transaction_type AS incomeType, lwd.payee_owner AS OWNER, "
			+ " lwd.money, lo.goods_price AS flowPrice, '订单' AS incomeTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_order lo ON lo.sn = lwd.sn WHERE "
			+ " lwd.payee_owner LIKE 'PROMOTE%' AND lo.id IS NOT NULL UNION ALL SELECT "
			+ " lt.payer AS memberId, lwd.payee AS payee, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS incomeTime, lwd.transaction_type AS incomeType, lwd.payee_owner AS OWNER, "
			+ " lwd.money, lt.transfer_money AS flowPrice, '转账' AS incomeTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_transfer lt ON lt.sn = lwd.sn WHERE "
			+ " lwd.payee_owner LIKE 'PROMOTE%' AND lt.id IS NOT NULL UNION ALL SELECT "
			+ " lmwa.member_id AS memberId, lwd.payee AS payee, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS incomeTime, lwd.transaction_type AS incomeType, lwd.payee_owner AS OWNER, "
			+ " lwd.money AS money, lmwa.apply_price AS flowPrice, '提现' AS incomeTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_member_withdraw_apply lmwa ON lmwa.sn = lwd.sn WHERE "
			+ " lwd.payee_owner LIKE 'PROMOTE%' AND lmwa.id IS NOT NULL UNION ALL SELECT "
			+ " lr.member_id AS memberId, lwd.payee AS payee, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS incomeTime, lwd.transaction_type AS incomeType, lwd.payee_owner AS OWNER, "
			+ " lwd.money AS money, lr.recharge_money AS flowPrice, '会员费' AS incomeTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_recharge lr ON lr.recharge_sn = lwd.sn WHERE "
			+ " lwd.payee_owner LIKE 'PROMOTE%' AND lr.id IS NOT NULL) AS a "
			+ " LEFT JOIN li_member lm ON a.memberId = lm.id LEFT JOIN li_membership_card lmc ON lmc.id = lm.grade_id"
			+ " ${ew.customSqlSegment}) as b")
	Map getIncomePromoteByPageCount(@Param(Constants.WRAPPER) QueryWrapper<MemberIncomeVo> queryWrapper);

	@Select("SELECT lm.id AS memberId, lm.nick_name AS nickName, lm.mobile AS mobile, "
			+ "lmc.NAME AS gradeIdName, lm.location AS location, a.memberId as payee, a.payer, "
			+ " a.receivableNo, DATE_FORMAT(a.expenditureTime,'%Y-%m-%d %H:%i:%S') AS expenditureTime,"
			+ " a.expenditureType, a.OWNER, RoundDown(sum(a.money), 2 ) AS money, "
			+ " RoundDown( a.handlingMoney, 2 ) AS handlingMoney, RoundDown( a.realMoney, 2 ) AS realMoney, "
			+ " RoundDown( a.fee, 2 ) AS fee, a.expenditureTypeName FROM ( SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, lo.order_type AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lo.commission AS handlingMoney, 0 AS realMoney, "
			+ " 0 AS fee , '订单' as expenditureTypeName FROM li_wallet_detail lwd "
			+ " LEFT JOIN li_order lo ON lo.sn = lwd.sn WHERE lwd.payer_owner LIKE 'PROMOTE%' "
			+ " AND lo.id IS NOT NULL UNION ALL SELECT lwd.payee AS memberId, lwd.payer, "
			+ " lwd.sn AS receivableNo, lwd.create_time AS expenditureTime, 'TRANSFER' AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lt.commission AS handlingMoney, "
			+ " 0 AS realMoney, 0 AS fee , '转账' as expenditureTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_transfer lt ON lt.sn = lwd.sn WHERE "
			+ " lwd.payer_owner LIKE 'PROMOTE%' AND lt.id IS NOT NULL UNION ALL SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, 'WITHDRAWAL' AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lmwa.handling_money AS handlingMoney, "
			+ " lmwa.real_money AS realMoney, lmwa.fee AS fee , '提现' as expenditureTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_member_withdraw_apply lmwa ON lmwa.sn = lwd.sn WHERE "
			+ " lwd.payer_owner LIKE 'PROMOTE%' AND lmwa.id IS NOT NULL UNION ALL SELECT"
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo,"
			+ " lwd.create_time AS expenditureTime, 'MEMBER' AS expenditureType, lwd.payer_owner AS OWNER,"
			+ " lwd.money AS money, 0 AS handlingMoney, 0 AS realMoney, 0 AS fee,"
			+ " '会员费' AS expenditureTypeName FROM li_wallet_detail lwd"
			+ " LEFT JOIN li_recharge lr ON lr.recharge_sn = lwd.sn WHERE lwd.payer_owner LIKE 'PROMOTE%' "
			+ " AND lr.id IS NOT NULL ) AS a LEFT JOIN li_member lm ON lm.id = a.payer "
			+ " LEFT JOIN li_membership_card lmc ON lmc.id = lm.grade_id ${ew.customSqlSegment}")
	IPage<MemberExpenditureVo> getBranchPromoteByPage(Page<Object> initPage,
			@Param(Constants.WRAPPER) QueryWrapper<MemberExpenditureVo> queryWrapper);

	@Select("SELECT RoundDown( sum(b.money), 2 ) AS sumMoney,"
			+ " RoundDown( sum(b.handlingMoney), 2 ) AS sumHandlingMoney,"
			+ " RoundDown( sum(b.realMoney), 2 ) AS sumRealMoney FROM ("
			+ "SELECT RoundDown ( sum( a.money ), 2 ) AS money,a.handlingMoney,a.realMoney FROM ( SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, lo.order_type AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lo.commission AS handlingMoney, "
			+ " 0 AS realMoney, 0 AS fee , '订单' as expenditureTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_order lo ON lo.sn = lwd.sn WHERE "
			+ " lwd.payer_owner LIKE 'PROMOTE%' AND lo.id IS NOT NULL UNION ALL SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, 'TRANSFER' AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lt.commission AS handlingMoney, "
			+ " 0 AS realMoney, 0 AS fee , '转账' as expenditureTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_transfer lt ON lt.sn = lwd.sn WHERE "
			+ " lwd.payer_owner LIKE 'PROMOTE%' AND lt.id IS NOT NULL UNION ALL SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, 'WITHDRAWAL' AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lmwa.handling_money AS handlingMoney, "
			+ " lmwa.real_money AS realMoney, lmwa.fee AS fee , '提现' as expenditureTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_member_withdraw_apply lmwa ON lmwa.sn = lwd.sn WHERE "
			+ " lwd.payer_owner LIKE 'PROMOTE%' AND lmwa.id IS NOT NULL UNION ALL SELECT"
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo,"
			+ " lwd.create_time AS expenditureTime, 'MEMBER' AS expenditureType, lwd.payer_owner AS OWNER,"
			+ " lwd.money AS money, 0 AS handlingMoney, 0 AS realMoney, 0 AS fee,"
			+ " '会员费' AS expenditureTypeName FROM li_wallet_detail lwd"
			+ " LEFT JOIN li_recharge lr ON lr.recharge_sn = lwd.sn WHERE lwd.payer_owner LIKE 'PROMOTE%' "
			+ " AND lr.id IS NOT NULL ) AS a LEFT JOIN li_member lm ON lm.id = a.payer "
			+ " LEFT JOIN li_membership_card lmc ON lmc.id = lm.grade_id ${ew.customSqlSegment}) as b")
	Map getBranchPromoteByPageCount(@Param(Constants.WRAPPER) QueryWrapper<MemberExpenditureVo> queryWrapper);

	@Select(" SELECT lm.id AS memberId, lm.nick_name AS nickName, lm.mobile AS mobile, "
			+ " IFNULL(lmc.name,'普通会员') AS gradeName, lm.location AS location, a.payee, a.receivableNo,"
			+ " DATE_FORMAT(a.incomeTime,'%Y-%m-%d %H:%i:%S') AS incomeTime, a.OWNER,"
			+ " RoundDown ( a.money, 2 ) AS money, RoundDown ( a.freightPrice, 2 ) AS freightPrice ,"
			+ " RoundDown ( a.AllMoney, 2 ) AS AllMoney , a.incomeTypeName FROM (SELECT "
			+ " lwd.payer AS memberId, lwd.payee AS payee, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS incomeTime, lwd.transaction_type AS incomeType, "
			+ " lwd.payee_owner AS OWNER, IFNULL(lo.freight_price,0) AS freightPrice, IFNULL(lo.goods_price,0) AS money , "
			+ " IFNULL(lo.freight_price,0) + IFNULL(lo.goods_price,0) AS AllMoney , '订单' AS incomeTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_order lo ON lo.sn = lwd.sn WHERE "
			+ " lwd.payee_owner = 'SALE' AND lo.id IS NOT NULL and lwd.transaction_type != 'WALLET_COLLECT' ) AS a "
			+ " LEFT JOIN li_member lm ON a.memberId = lm.id "
			+ " LEFT JOIN li_membership_card lmc ON lmc.id = lm.grade_id ${ew.customSqlSegment}")
	IPage<MemberIncomeVo> getIncomeSaleByPage(Page<Object> initPage,
			@Param(Constants.WRAPPER) QueryWrapper<MemberIncomeVo> queryWrapper);

	@Select("SELECT RoundDown(sum(a.money),2) AS sumMoney, "
			+ " RoundDown(sum(a.freightPrice) ,2) AS sumFreightPrice, RoundDown(sum(a.AllMoney) ,2) AS sumAllMoney "
			+ " FROM (SELECT lwd.payer AS memberId, lwd.payee AS payee, "
			+ " lwd.sn AS receivableNo, lwd.create_time AS incomeTime, lwd.transaction_type AS incomeType, "
			+ " lwd.payee_owner AS OWNER, IFNULL(lo.freight_price,0) AS freightPrice, IFNULL(lo.goods_price,0) AS money , "
			+ " IFNULL(lo.freight_price,0) + IFNULL(lo.goods_price,0) AS AllMoney , '订单' AS incomeTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_order lo ON lo.sn = lwd.sn WHERE "
			+ " lwd.payee_owner = 'SALE' AND lo.id IS NOT NULL and lwd.transaction_type != 'WALLET_COLLECT' ) AS a "
			+ " LEFT JOIN li_member lm ON a.memberId = lm.id LEFT JOIN li_membership_card lmc ON lmc.id = lm.grade_id"
			+ " ${ew.customSqlSegment}")
	Map getIncomeSaleByPageCount(@Param(Constants.WRAPPER) QueryWrapper<MemberIncomeVo> queryWrapper);

	@Select(" SELECT lm.id AS memberId, lm.nick_name AS nickName, lm.mobile AS mobile, "
			+ " lmc.NAME AS gradeIdName, lm.location AS location, a.memberId as payee, a.payer, "
			+ " a.receivableNo, DATE_FORMAT(a.expenditureTime,'%Y-%m-%d %H:%i:%S') AS expenditureTime,"
			+ " a.expenditureType, a.OWNER, RoundDown( sum(a.money), 2 ) AS money, "
			+ " RoundDown( a.handlingMoney, 2 ) AS handlingMoney, RoundDown( a.realMoney, 2 ) AS realMoney, "
			+ " RoundDown( a.fee, 2 ) AS fee, a.expenditureTypeName FROM ( SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, lo.order_type AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lo.commission AS handlingMoney, "
			+ " 0 AS realMoney, 0 AS fee , '订单' as expenditureTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_order lo ON lo.sn = lwd.sn WHERE "
			+ " lwd.payer_owner = 'SALE' AND lo.id IS NOT NULL UNION ALL SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, 'TRANSFER' AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lt.commission AS handlingMoney, "
			+ " 0 AS realMoney, 0 AS fee , '转账' as expenditureTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_transfer lt ON lt.sn = lwd.sn WHERE "
			+ " lwd.payer_owner = 'SALE' AND lt.id IS NOT NULL UNION ALL SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, 'WITHDRAWAL' AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lmwa.handling_money AS handlingMoney, "
			+ " lmwa.real_money AS realMoney, lmwa.fee AS fee , '提现' as expenditureTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_member_withdraw_apply lmwa ON lmwa.sn = lwd.sn WHERE "
			+ " lwd.payer_owner = 'SALE' AND lmwa.id IS NOT NULL UNION ALL SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, 'MEMBER' AS expenditureType, lwd.payer_owner AS OWNER, "
			+ " lwd.money AS money, 0 AS handlingMoney, 0 AS realMoney, 0 AS fee, "
			+ " '会员费' AS expenditureTypeName FROM li_wallet_detail lwd "
			+ " LEFT JOIN li_recharge lr ON lr.recharge_sn = lwd.sn WHERE lwd.payer_owner = 'SALE' "
			+ " AND lr.id IS NOT NULL ) AS a LEFT JOIN li_member lm ON lm.id = a.payer "
			+ " LEFT JOIN li_membership_card lmc ON lmc.id = lm.grade_id ${ew.customSqlSegment}")
	IPage<MemberExpenditureVo> getBranchSaleByPage(Page<Object> initPage,
			@Param(Constants.WRAPPER) QueryWrapper<MemberExpenditureVo> queryWrapper);

	@Select("SELECT RoundDown( sum(b.money), 2 ) AS sumMoney,"
			+ " RoundDown( sum(b.handlingMoney), 2 ) AS sumHandlingMoney,"
			+ " RoundDown( sum(b.realMoney), 2 ) AS sumRealMoney FROM ("
			+ "SELECT RoundDown ( sum( a.money ), 2 ) AS money,a.handlingMoney,a.realMoney FROM ( SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, lo.order_type AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lo.commission AS handlingMoney, "
			+ " 0 AS realMoney, 0 AS fee , '订单' as expenditureTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_order lo ON lo.sn = lwd.sn WHERE "
			+ " lwd.payer_owner = 'SALE' AND lo.id IS NOT NULL UNION ALL SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, 'TRANSFER' AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lt.commission AS handlingMoney, "
			+ " 0 AS realMoney, 0 AS fee , '转账' as expenditureTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_transfer lt ON lt.sn = lwd.sn WHERE "
			+ " lwd.payer_owner = 'SALE' AND lt.id IS NOT NULL UNION ALL SELECT "
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo, "
			+ " lwd.create_time AS expenditureTime, 'WITHDRAWAL' AS expenditureType, "
			+ " lwd.payer_owner AS OWNER, lwd.money AS money, lmwa.handling_money AS handlingMoney, "
			+ " lmwa.real_money AS realMoney, lmwa.fee AS fee , '提现' as expenditureTypeName FROM "
			+ " li_wallet_detail lwd LEFT JOIN li_member_withdraw_apply lmwa ON lmwa.sn = lwd.sn WHERE "
			+ " lwd.payer_owner = 'SALE' AND lmwa.id IS NOT NULL UNION ALL SELECT"
			+ " lwd.payee AS memberId, lwd.payer, lwd.sn AS receivableNo,"
			+ " lwd.create_time AS expenditureTime, 'MEMBER' AS expenditureType, lwd.payer_owner AS OWNER,"
			+ " lwd.money AS money, 0 AS handlingMoney, 0 AS realMoney, 0 AS fee,"
			+ " '会员费' AS expenditureTypeName FROM li_wallet_detail lwd"
			+ " LEFT JOIN li_recharge lr ON lr.recharge_sn = lwd.sn WHERE lwd.payer_owner = 'SALE' "
			+ " AND lr.id IS NOT NULL ) AS a LEFT JOIN li_member lm ON lm.id = a.payer "
			+ " LEFT JOIN li_membership_card lmc ON lmc.id = lm.grade_id ${ew.customSqlSegment}) as b")
	Map getBranchSaleByPageCount(@Param(Constants.WRAPPER) QueryWrapper<MemberExpenditureVo> queryWrapper);

	@Select(" SELECT m.*,s.id as storeId,s.store_name,s.store_logo from li_member m "
			+ " left join li_store s on s.member_id = m.id ${ew.customSqlSegment}")
	List<MemberVO> getUserInfoById(@Param(Constants.WRAPPER) QueryWrapper<MemberVO> queryWrapper);

	@Select(" select * from li_partner a left join li_member b on a.member_id = b.id ${ew.customSqlSegment}")
	List<Member> getShiYe(@Param(Constants.WRAPPER) QueryWrapper<Member> queryWrapper);

	@Select("SELECT DISTINCT a.*,m1.real_name AS nameExte, m1.mobile AS mobileExte,b.end_time MemberEndTime,m.LEVEL gradeName,m.name gradeText,w1.member_wallet rechargeMoney ,p.partner_type"
			+ " FROM li_member a LEFT JOIN li_grade_level b ON a.id = b.member_id and b.delete_flag = '0' "
			+ " LEFT JOIN li_membership_card m ON m.id = b.grade_id"
			+ " LEFT JOIN li_member_wallet w1 ON w1.member_id = a.id and w1.owner = 'RECHARGE'"
//			+ " LEFT JOIN li_member_wallet w2 ON w2.member_id = a.id and w2.owner = 'PROMOTE' "
			+ " LEFT JOIN li_partner p ON p.member_id = a.id "
			+ " LEFT JOIN li_extension e1 ON e1.member_id = a.id AND e1.delete_flag = 0 "
			+ " LEFT JOIN li_member m1 ON e1.extension = m1.promotion_code  "
			+ " ${ew.customSqlSegment}")
	IPage<MemberVO> pageByCmember(IPage<MemberVO> page, @Param(Constants.WRAPPER) Wrapper<Member> queryWrapper);

}
